library(here)
library(readxl)
library(dplyr)
library(BelgiumMaps.StatBel)
library(sf)
library(tmap)
library(leaflet)
library(leaflet.minicharts)
library(htmltools)
dynam.prov = read_excel(
    '20180425_dynam_be_jobdata_origineel.xlsx', 'Sheet2',
    skip = 1,  col_names = c(
      'province_lbl', 'province_nis_code', 
      'absoluut_bruto_toename', 'absoluut_bruto_afname', 'absoluut_netto', 'delme',
      'delmetoo', 'bruto_toename_pct', 'bruto_afname_pct', 'netto_pct')) %>%
  select(-delme, -delmetoo)
dynam.prov
dynam.prov = dynam.prov %>%
  mutate(
    netto_pct = netto_pct*100,
    absoluut_bruto_afname = absoluut_bruto_afname*-1)
data('BE_ADMIN_PROVINCE') 
data("BE_ADMIN_REGION")
# convert to simple features dataset-structure
provinces = st_as_sf(BE_ADMIN_PROVINCE) 
regions = st_as_sf(BE_ADMIN_REGION) 
# Spatial object for Brussels-region is not included in provences, add from region
provinces = rbind(
  provinces %>% select(CD_PROV_REFNIS, TX_PROV_DESCR_NL),
  regions %>%
    filter(TX_RGN_DESCR_NL == "Brussels Hoofdstedelijk Gewest") %>%
    mutate(CD_RGN_REFNIS = '04000') %>%
    select(
      CD_PROV_REFNIS = CD_RGN_REFNIS,
      TX_PROV_DESCR_NL = TX_RGN_DESCR_NL))
provinces = provinces %>%
  mutate(CD_PROV_REFNIS = as.character(CD_PROV_REFNIS)) %>%
  left_join(dynam.prov, c('CD_PROV_REFNIS' = 'province_nis_code'))
pal <- colorBin("Greens", domain = dynam.prov$netto_pct)
# get centroid coordinates for each province to plot barchart there
provinces_coords = st_coordinates(st_centroid(provinces))
d.abs = dynam.prov %>%
  select(
    'Jobgroei' = absoluut_bruto_toename,
    'Jobverlies' = absoluut_bruto_afname,
    'Netto jobevolutie' = absoluut_netto)
m.prov = leaflet(provinces, width = '100%') %>% 
  # add grey arrondissement polygons w/t white border
  addPolygons(
    weight = 2,
    opacity = 1,
    dashArray = "3",
    fillColor = ~pal(netto_pct), color = 'white') %>% 
  addLegend(pal = pal, values = ~absoluut_netto, opacity = 0.7,
            title = 'Netto jobevolutie (%)',
  position = "bottomright")
colors <- c("#7cae00", "#f8766d", "#c77cff")
m.prov = m.prov %>%
  addMinicharts(
    provinces_coords[,1], provinces_coords[,2],
    chartdata = d.abs,
    colorPalette = colors,
    width = 45, height = 45)
map_title <- tags$div(
   HTML('<b>Bruto en netto jobevolutie per provincie, 2017-2018 (<a href="https://dynamresearch.be/">DynaM</a>)</b>')
 )  
m.prov = m.prov %>% 
  addControl(map_title, position = "bottomleft")
m.prov
LS0tDQp0aXRsZTogIkR5bmFtIGpvYmV2b2x1dGllIGthYXJ0amVzIg0Kb3V0cHV0OiANCiAgaHRtbF9ub3RlYm9vazogDQogICAgY29kZV9mb2xkaW5nOiBzaG93DQogICAgdGhlbWU6IGx1bWVuDQotLS0NCg0KYGBge3IsIG1lc3NhZ2U9RkFMU0UsIHdhcm5pbmc9RkFMU0V9DQpsaWJyYXJ5KGhlcmUpDQpsaWJyYXJ5KHJlYWR4bCkNCmxpYnJhcnkoZHBseXIpDQpsaWJyYXJ5KEJlbGdpdW1NYXBzLlN0YXRCZWwpDQpsaWJyYXJ5KHNmKQ0KbGlicmFyeSh0bWFwKQ0KbGlicmFyeShsZWFmbGV0KQ0KbGlicmFyeShsZWFmbGV0Lm1pbmljaGFydHMpDQpsaWJyYXJ5KGh0bWx0b29scykNCmBgYA0KDQpgYGB7cn0NCmR5bmFtLnByb3YgPSByZWFkX2V4Y2VsKA0KICAgICcyMDE4MDQyNV9keW5hbV9iZV9qb2JkYXRhX29yaWdpbmVlbC54bHN4JywgJ1NoZWV0MicsDQogICAgc2tpcCA9IDEsICBjb2xfbmFtZXMgPSBjKA0KICAgICAgJ3Byb3ZpbmNlX2xibCcsICdwcm92aW5jZV9uaXNfY29kZScsIA0KICAgICAgJ2Fic29sdXV0X2JydXRvX3RvZW5hbWUnLCAnYWJzb2x1dXRfYnJ1dG9fYWZuYW1lJywgJ2Fic29sdXV0X25ldHRvJywgJ2RlbG1lJywNCiAgICAgICdkZWxtZXRvbycsICdicnV0b190b2VuYW1lX3BjdCcsICdicnV0b19hZm5hbWVfcGN0JywgJ25ldHRvX3BjdCcpKSAlPiUNCiAgc2VsZWN0KC1kZWxtZSwgLWRlbG1ldG9vKQ0KYGBgDQoNCmBgYHtyfQ0KZHluYW0ucHJvdg0KYGBgDQoNCg0KYGBge3IsIHdhcm5pbmc9RkFMU0V9DQpkeW5hbS5wcm92ID0gZHluYW0ucHJvdiAlPiUNCiAgbXV0YXRlKA0KICAgIG5ldHRvX3BjdCA9IG5ldHRvX3BjdCoxMDAsDQogICAgYWJzb2x1dXRfYnJ1dG9fYWZuYW1lID0gYWJzb2x1dXRfYnJ1dG9fYWZuYW1lKi0xKQ0KYGBgDQoNCmBgYHtyfQ0KZGF0YSgnQkVfQURNSU5fUFJPVklOQ0UnKSANCmRhdGEoIkJFX0FETUlOX1JFR0lPTiIpDQoNCiMgY29udmVydCB0byBzaW1wbGUgZmVhdHVyZXMgZGF0YXNldC1zdHJ1Y3R1cmUNCnByb3ZpbmNlcyA9IHN0X2FzX3NmKEJFX0FETUlOX1BST1ZJTkNFKSANCnJlZ2lvbnMgPSBzdF9hc19zZihCRV9BRE1JTl9SRUdJT04pIA0KDQojIFNwYXRpYWwgb2JqZWN0IGZvciBCcnVzc2Vscy1yZWdpb24gaXMgbm90IGluY2x1ZGVkIGluIHByb3ZlbmNlcywgYWRkIGZyb20gcmVnaW9uDQpwcm92aW5jZXMgPSByYmluZCgNCiAgcHJvdmluY2VzICU+JSBzZWxlY3QoQ0RfUFJPVl9SRUZOSVMsIFRYX1BST1ZfREVTQ1JfTkwpLA0KICByZWdpb25zICU+JQ0KICAgIGZpbHRlcihUWF9SR05fREVTQ1JfTkwgPT0gIkJydXNzZWxzIEhvb2Zkc3RlZGVsaWprIEdld2VzdCIpICU+JQ0KICAgIG11dGF0ZShDRF9SR05fUkVGTklTID0gJzA0MDAwJykgJT4lDQogICAgc2VsZWN0KA0KICAgICAgQ0RfUFJPVl9SRUZOSVMgPSBDRF9SR05fUkVGTklTLA0KICAgICAgVFhfUFJPVl9ERVNDUl9OTCA9IFRYX1JHTl9ERVNDUl9OTCkpDQpgYGANCg0KYGBge3J9DQpwcm92aW5jZXMgPSBwcm92aW5jZXMgJT4lDQogIG11dGF0ZShDRF9QUk9WX1JFRk5JUyA9IGFzLmNoYXJhY3RlcihDRF9QUk9WX1JFRk5JUykpICU+JQ0KICBsZWZ0X2pvaW4oZHluYW0ucHJvdiwgYygnQ0RfUFJPVl9SRUZOSVMnID0gJ3Byb3ZpbmNlX25pc19jb2RlJykpDQpgYGANCg0KDQpgYGB7cn0NCnBhbCA8LSBjb2xvckJpbigiR3JlZW5zIiwgZG9tYWluID0gZHluYW0ucHJvdiRuZXR0b19wY3QpDQpgYGANCg0KYGBge3IsIHdhcm5pbmc9RkFMU0V9DQojIGdldCBjZW50cm9pZCBjb29yZGluYXRlcyBmb3IgZWFjaCBwcm92aW5jZSB0byBwbG90IGJhcmNoYXJ0IHRoZXJlDQpwcm92aW5jZXNfY29vcmRzID0gc3RfY29vcmRpbmF0ZXMoc3RfY2VudHJvaWQocHJvdmluY2VzKSkNCmBgYA0KDQpgYGB7cn0NCmQuYWJzID0gZHluYW0ucHJvdiAlPiUNCiAgc2VsZWN0KA0KICAgICdKb2Jncm9laScgPSBhYnNvbHV1dF9icnV0b190b2VuYW1lLA0KICAgICdKb2J2ZXJsaWVzJyA9IGFic29sdXV0X2JydXRvX2FmbmFtZSwNCiAgICAnTmV0dG8gam9iZXZvbHV0aWUnID0gYWJzb2x1dXRfbmV0dG8pDQpgYGANCg0KDQpgYGB7ciByZXN1bHRzPSJhc2lzIiwgZWNobz1GQUxTRX0NCmNhdCgiDQo8c3R5bGU+DQoubGVhZmxldC1jb250YWluZXIgew0KICAgIGJhY2tncm91bmQ6ICNGRkY7DQp9DQo8L3N0eWxlPg0KIikNCmBgYA0KDQpgYGB7cn0NCm0ucHJvdiA9IGxlYWZsZXQocHJvdmluY2VzLCB3aWR0aCA9ICcxMDAlJykgJT4lIA0KICAjIGFkZCBncmV5IGFycm9uZGlzc2VtZW50IHBvbHlnb25zIHcvdCB3aGl0ZSBib3JkZXINCiAgYWRkUG9seWdvbnMoDQogICAgd2VpZ2h0ID0gMiwNCiAgICBvcGFjaXR5ID0gMSwNCiAgICBkYXNoQXJyYXkgPSAiMyIsDQogICAgZmlsbENvbG9yID0gfnBhbChuZXR0b19wY3QpLCBjb2xvciA9ICd3aGl0ZScpICU+JSANCiAgYWRkTGVnZW5kKHBhbCA9IHBhbCwgdmFsdWVzID0gfmFic29sdXV0X25ldHRvLCBvcGFjaXR5ID0gMC43LA0KICAgICAgICAgICAgdGl0bGUgPSAnTmV0dG8gam9iZXZvbHV0aWUgKCUpJywNCiAgcG9zaXRpb24gPSAiYm90dG9tcmlnaHQiKQ0KDQpjb2xvcnMgPC0gYygiIzdjYWUwMCIsICIjZjg3NjZkIiwgIiNjNzdjZmYiKQ0KbS5wcm92ID0gbS5wcm92ICU+JQ0KICBhZGRNaW5pY2hhcnRzKA0KICAgIHByb3ZpbmNlc19jb29yZHNbLDFdLCBwcm92aW5jZXNfY29vcmRzWywyXSwNCiAgICBjaGFydGRhdGEgPSBkLmFicywNCiAgICBjb2xvclBhbGV0dGUgPSBjb2xvcnMsDQogICAgd2lkdGggPSA0NSwgaGVpZ2h0ID0gNDUpDQoNCm1hcF90aXRsZSA8LSB0YWdzJGRpdigNCiAgIEhUTUwoJzxiPkJydXRvIGVuIG5ldHRvIGpvYmV2b2x1dGllIHBlciBwcm92aW5jaWUsIDIwMTctMjAxOCAoPGEgaHJlZj0iaHR0cHM6Ly9keW5hbXJlc2VhcmNoLmJlLyI+RHluYU08L2E+KTwvYj4nKQ0KICkgIA0KDQptLnByb3YgPSBtLnByb3YgJT4lIA0KICBhZGRDb250cm9sKG1hcF90aXRsZSwgcG9zaXRpb24gPSAiYm90dG9tbGVmdCIpDQoNCm0ucHJvdg0KDQpgYGANCg0K